﻿
/*
 * 文件名 ：Fraction.cs
 * 类名 : XHCTMS.DAL.Fraction
 * 
 * 创建人 ：张夏
 * 创建时间 : 2013-05-28
 * 备注 ：对班级分表的操作。
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace XHCTMS.DAL
{
    /// <summary>
    /// 对班级分表的操作
    /// </summary>
    public  class Fraction
    {
        
        /// <summary>
        /// 获得部分表信息(用Search代替了）
        /// </summary>
        /// <param name="page">页面（50项）</param>
        /// <returns>返回类型DataTable</returns>
        public DataTable GetPart(int page)
        {
            StringBuilder sb = new StringBuilder();
            //班级，平均分，课程名，班主任，专业，完成时间
//            sb.Append(@"select * from( select top (50)* from( select top (50*@page) tcl.class_name,tfr.fra_average,
// tte.teaching_lesson,tus.[user_name],tsub.sub_name,tfr.fra_time
// from class tcl left join sub tsub on tcl.class_sub_id=tsub.sub_id  
// left join teaching tte on tcl.class_id = tte.teaching_class_Id 
// left join [user] tus on  tte.teaching_teacher_Id = tus.[user_id] 
// left join fraction tfr on tfr.fra_teaching_id=tte.teaching_id
// where tte.teaching_state =0 order by tsub.sub_name  ,tcl.class_name ) A order by A.sub_name desc ,A.class_name desc) B
//order by B.sub_name,B.class_name");

            sb.Append(@"select top 50  tfr.fra_id,tsub.sub_name,tcl.class_name,tte.teaching_lesson,tfr.fra_average,
            tus.[user_name],tfr.fra_time from class tcl left join sub tsub on tcl.class_sub_id=tsub.sub_id  
            left join teaching tte on tcl.class_id = tte.teaching_class_Id 
            left join [user] tus on  tte.teaching_teacher_Id = tus.[user_id] 
            left join fraction tfr on tfr.fra_teaching_id=tte.teaching_id where tte.teaching_state =-1
            and tfr.fra_id not in (select top (50*@page) tfr.fra_id from class tcl left join sub tsub on tcl.class_sub_id=tsub.sub_id  
            left join teaching tte on tcl.class_id = tte.teaching_class_Id 
            left join [user] tus on  tte.teaching_teacher_Id = tus.[user_id] 
            left join fraction tfr on tfr.fra_teaching_id=tte.teaching_id)");
            SqlParameter[] paras = { new SqlParameter("@page", SqlDbType.Int) };
            paras[0].Value = page -1;
            DataTable dt = new DBHELP.Common().GetDataTable(sb.ToString(), paras);
            return dt;
        }


        #region 过期 结课打分方法
        //        /// <summary>
//        /// 为结课课程打分
//        /// </summary>
//        /// <param name="teachingID">授课任务号（注：状态为：结课没打分.。自己做好验证，否则帮你结课）</param>
//        /// <returns>返回类型Int 成功/1； 失败/0； 异常 /-1；</returns>
//        public  int Add(int teachingID )
//        {
//            float fraScore = 0;
//            StringBuilder sb = new StringBuilder();
//            SqlParameter[] paras = new SqlParameter[] { };

//            sb.Append(@"select COUNT(student_id) from student where student_class_Id = 
//                (select  teaching_class_Id from teaching where teaching_id =@teachingID) and student_state =1");
//            paras = new SqlParameter[] { new SqlParameter("@teachingID", SqlDbType.Int) };
//            paras[0].Value = teachingID;
//            //统计在校班级人数
//            int classNumber = int.Parse(new DBHELP.Common().GetDataTable(sb.ToString(), paras).Rows[0][0].ToString());

//            sb.Clear();
//            //计算在校学生的总分（平时分占10%,考试分占90%)
//            sb.Append(@" select (sum(tsu.suc_per_score)+sum(tsu.suc_checking_score))*0.1 + sum(tsu.suc_score)*0.9 from success tsu left join student tstu
//             on tstu.student_id = tsu.suc_student_id left join teaching  tte on tsu.suc_teaching_id = tte.teaching_id where tstu.student_state =1 and tte.teaching_id = @teachingID
//             group by suc_teaching_id   ");

//            float totalSorce = float.Parse(new DBHELP.Common().GetDataTable(sb.ToString(), paras).Rows[0][0].ToString());
//            fraScore = float.Parse((totalSorce / classNumber).ToString("0.00"));
//            sb.Clear();
//            sb.Append(@"insert into fraction values(@teachingID,@fraScore,CONVERT(varchar(32), GETDATE(), 23));
//            update Teaching set teaching_state = 0 where teaching_id = @teachingID");
//             paras = new SqlParameter[] {
//                                   new SqlParameter("@teachingID",SqlDbType.Int),
//                                   new SqlParameter("@fraScore",SqlDbType.VarChar,32)
//                                   };
//            paras[0].Value = teachingID;
//            paras[1].Value = fraScore;
//           int reslut = new DBHELP.Common().ExcuteQuery(sb.ToString(), paras);
//           return reslut;
        //        }
        #endregion

        /// <summary>
       /// 分页按条件查询
       /// </summary>
       /// <param name="parasValue">条件参数（班级名，班主任名，授课教师名，专业，课程）</param>
       /// <param name="page">页数 （默认1；每页 50项）</param>
       /// <returns>返回类型DataTable </returns>
        public DataTable Search(string[] parasValue,int page)
        {

            DataTable dt = new DataTable();
            string[] parasStr = { " tcl.class_name =", " tclu.[user_name] =", " tteu.[user_name] ="," tsub.sub_name =",
                                   " tte.teaching_lesson like "};
            string[] parasName = { "@className", "@classTeaName", "@TeachingTeaName", "@subName", "@classLesson" };
            if (parasValue.Length > parasStr.Length)
            {
                dt.TableName = "ArraryLengthError";
                return dt;
            }
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            List<SqlParameter> paras = new List<SqlParameter> { };
//            sb.Append(@"select * from( select top (50)* from( select top (50*@page) tcl.class_name,tfr.fra_average,
//                         tte.teaching_lesson,tus.[user_name],tsub.sub_name,tfr.fra_time
//                         from class tcl left join sub tsub on tcl.class_sub_id=tsub.sub_id  
//                         left join teaching tte on tcl.class_id = tte.teaching_class_Id 
//                         left join [user] tus on  tte.teaching_teacher_Id = tus.[user_id] 
//                         left join fraction tfr on tfr.fra_teaching_id=tte.teaching_id
//                         where tte.teaching_state =0  ");
            sb.Append(@"select top 50  tsub.sub_name+' '+CONVERT(varchar(2),tsub.sub_years)+'年制 '+tcl.class_name as class_allname,
           tte.teaching_lesson, tclu.[user_name] as class_tea,
           tteu.[user_name] as teaching_tea, tfr.fra_average,tfr.fra_time
           from  fraction tfr left join teaching tte on tfr.fra_teaching_id= tte.teaching_id
           left join class tcl on tte.teaching_class_Id = tcl.class_id 
           left join [user] tclu on tclu.[user_id] = tcl.class_teacher_id
           left join [user] tteu on tteu.[user_id] = tte.teaching_teacher_Id
           left join sub tsub on tsub.sub_id = tcl.class_sub_id where tcl.class_flag = 1 ");
            sb2.Append(@" and tfr.fra_id not in (select top (50*@page) tfr.fra_id
          from  fraction tfr left join teaching tte on tfr.fra_teaching_id= tte.teaching_id
          left join class tcl on tte.teaching_class_Id = tcl.class_id 
          left join [user] tclu on tclu.[user_id] = tcl.class_teacher_id
          left join [user] tteu on tteu.[user_id] = tte.teaching_teacher_Id
          left join sub tsub on tsub.sub_id = tcl.class_sub_id where tcl.class_flag = 1 ");

         
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    if (count == 4 )
                    {
                        StringBuilder arrSb = new StringBuilder();
                        for (int i = 0; i < parasValue[count].Length; i++)
                        {
                            arrSb.Append("%" + parasValue[count].Substring(i, 1));
                        }
                        arrSb.Append("%");
                        parasValue[count] = arrSb.ToString();
                    }
                    sb.Append(" and " + parasStr[count] + parasName[count]);
                    sb2.Append(" and " + parasStr[count] + parasName[count]);
                    paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                }
            }
            sb.Append(sb2.ToString());
            sb.Append(@" order by tsub.sub_name,tsub.sub_years,tcl.class_name,tte.teaching_lesson )
 order by class_allname,tte.teaching_lesson");
            paras.Add(new SqlParameter("@page",page -1));
            dt = new DBHELP.Common().GetDataTable(sb.ToString(), paras);
            return dt;
        }

        /// <summary>
        /// 获取满足条件的所有班级成绩信息的页数（每页50条，在校班级）
        /// </summary>
        /// <param name="parasValue"></param>
        /// <returns></returns>
        public int MaxPage(string[] parasValue)
        {
            string[] parasStr = { " tcl.class_name=", " tclu.[user_name] =", " tteu.[user_name] ="," tsub.sub_name=",
                                   " tte.teaching_lesson like "," tstu.student_name like "};
            string[] parasName = { "@className", "@classTeaName", "@TeachingTeaName", "@subName", "@classLesson", "@studentName" };
            if (parasValue.Length > parasStr.Length)
            {
                return -11;
            }
            List<SqlParameter> lstParas = new List<SqlParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select COUNT(tfr.fra_id)  from  fraction tfr 
           left join teaching tte on tfr.fra_teaching_id= tte.teaching_id
           left join class tcl on tte.teaching_class_Id = tcl.class_id 
           left join [user] tclu on tclu.[user_id] = tcl.class_teacher_id
           left join [user] tteu on tteu.[user_id] = tte.teaching_teacher_Id
           left join sub tsub on tsub.sub_id = tcl.class_sub_id where tcl.class_flag = 1 ");
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    if (count == 4)
                    {
                        StringBuilder arrSb = new StringBuilder();
                        for (int i = 0; i < parasValue[count].Length; i++)
                        {
                            arrSb.Append("%" + parasValue[count].Substring(i, 1));
                        }
                        arrSb.Append("%");
                        parasValue[count] = arrSb.ToString();
                    }
                    sb.Append(" and " + parasStr[count] + parasName[count]);
                   
                    lstParas.Add(new SqlParameter(parasName[count], parasValue[count]));
                }
            }
            int number = int.Parse(new DBHELP.Common().GetDataScalar(sb.ToString(), lstParas));
            return (number - 1) / 50 + 1;

        }



 

    }
}
